﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
using INTEX.DataBase.Tools;
using ReflectionStudio.Core.Events;
namespace WPFExcelReport
{    
    public partial class JLTAB55Data : BaseFunction
    {
        ApplicationBase app
        {
            get
            {
                return ApplicationBase.Instance;
            }
        }
        INTEXDBC INTEXDBC
        {
            get
            {
                return app.INTEXDBC;
            }
        } 
        public JLTAB55Data()
            : base()
        {
        }
         public JLTAB55Data(Dictionary<string, object> DicGlobalPara)
            : base(DicGlobalPara)
        {
        }

        public DataSet GetAB55(DataRow dwTABEL)
        {
            DataSet Result;
            DataSet Return = new DataSet();
            string TableName = "AB55";
            string KRS = dwTABEL["KRS"].ToString().Trim();
            string LFD = dwTABEL["LFD"].ToString().Trim();
            string POS = dwTABEL["POS"].ToString().Trim();
            try
            {
                orlReadCommand = INTEXDBC.OracleDB.CreateCommand();
                orlReadCommand.CommandText = "select A.ABPAUFKRS||'-'||TO_NUMBER(A.ABPAUFLFD)||'-'||TO_NUMBER(A.ABPAUFPOS) AS WVORDER,' ' AS WPATTERN,' ' AS FPATTERN,"
                    + " TRIM(A.ABPART)||'-'||TRIM(A.ABPFARBE) ARTICLE,TRIM(B.ABKKDAUFTNR) CO, ABPART,ABPVAR,ABPFARBE,"
                    + " A.ABPSOLLMENGE2*0.01 AS ORDQTY,A.ABPZUGET*0.01 AS ALLQTY,A.ABPANZSTK, "
                    + " DECODE(A.ABPSTKLG,0,C.WARSTDLG*0.001,A.ABPSTKLG*0.01) AS PQTY, "
                    + " A.ABPSTARTDAT, A.ABPENDDAT,C.WARFDDIK*0.01 AS FDDIK,C.WARFDDIS*0.01 AS FDDIS, "
                    + " (SELECT TRIM(SUBSTR(SDDATEN,2,20)) FROM SDDAT WHERE SDSA = '0178' AND SDKEY LIKE '00%V'  "
                    + " AND SUBSTR(SDKEY,3,3)=D.VAAARTGRP) AS DES, "
                    + " (SELECT distinct TRIM(SUBSTR(SDDATEN,52,10)) FROM SDDAT WHERE SDSA = '0734'  "
                    + " AND SUBSTR(SDKEY,3,1)=D.VAAALTERNAT) AS STR, "
                    + " (SELECT TRIM(SUBSTR(TXTKTEXT,INSTR(TXTKTEXT,' '),50)) FROM TXTIND T1,TXTKON T2 "
                    + " WHERE T1.TXTIMANDANT = T2.TXTKMANDANT AND T1.TXTIPIND = T2.TXTKPIND "
                    + " AND T1.TXTITXTART = 'VAD' AND TRIM(T1.TXTISIND) = TRIM(D.VAAKOLL||D.VAAART) ) AS YARN, "
                    + " E.BZBAUMKRS||'-'||TO_NUMBER(E.BZBAUMLFD) AS BEAM,TO_NUMBER(E.BZMLFDNR) AS MACHINE "
                    + " from abpos a,ABKOPF B,WASTAMM C, VAART D,BAZU E "
                    + " where a.abpaufkrs = '"+KRS+"' AND A.ABPAUFLFD = '"+LFD+"' AND A.ABPAUFPOS = '"+POS+"' "
                    + " AND A.ABPMANDANT = B.ABKMANDANT AND A.ABPAUFKRS = B.ABKAUFKRS "
                    + " AND A.ABPAUFLFD = B.ABKAUFLFD AND A.ABPMANDANT =  C.WAMANDANT "
                    + " AND A.ABPKUNDNR = C.WAKUNDNR AND A.ABPWERK = C.WAWERK "
                    + " AND A.ABPABT = C.WAABT AND A.ABPART = C.WAART "
                    + " AND A.ABPVAR = C.WAVAR||' ' AND A.ABPMANDANT = D.VAAMANDANT "
                    + " AND TRIM(A.ABPART) = TRIM(D.VAAART) "
                    + " AND A.ABPMANDANT = E.BZMANDANT(+) AND A.ABPAUFKRS = E.BZAUFKRS(+) "
                    + " AND A.ABPAUFLFD = E.BZAUFLFD(+) AND A.ABPAUFPOS = E.BZAUFPOS(+) "                   
                    ;

                Result = INTEXDBC.Connect2Proxy(orlReadCommand);
                if (Result.Tables.Count > 0)
                {
                    Result.Tables[0].TableName = TableName;
                    byte[] b;
                    string value;
                    foreach (DataRow dw in Result.Tables[TableName].Rows)
                    {
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw["DES"].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw["DES"] = value;
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw["STR"].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw["STR"] = value;
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw["YARN"].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw["YARN"] = value;
                    }
                }
                Return.Merge(Result);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return Return;
        }
        public DataSet GetTT(DataRow dwTABEL)
        {
            DataSet Result;
            DataSet Return = new DataSet();
            string TableName = "TT";
            string KRS = dwTABEL["KRS"].ToString().Trim();
            string LFD = dwTABEL["LFD"].ToString().Trim();
            string POS = dwTABEL["POS"].ToString().Trim();
            try
            {
                orlReadCommand = INTEXDBC.OracleDB.CreateCommand();
                orlReadCommand.CommandText = " SELECT TRIM(TXTKTEXT) AS REMARK FROM TXTIND T1,TXTKON T2 "
                    + " WHERE T1.TXTIMANDANT = T2.TXTKMANDANT AND T1.TXTIPIND = T2.TXTKPIND "
                    + " AND T1.TXTITXTART = 'ABP' AND TRIM(T1.TXTISIND) = '"+KRS+LFD+POS+"' "
                    ;

                Result = INTEXDBC.Connect2Proxy(orlReadCommand);
                if (Result.Tables.Count > 0)
                {
                    Result.Tables[0].TableName = TableName;
                    byte[] b;
                    string value;
                    foreach (DataRow dw in Result.Tables[TableName].Rows)
                    {
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw["REMARK"].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw["REMARK"] = value;
                    }
                }
                Return.Merge(Result);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return Return;
        }
        public DataSet GetWB(DataRow dwTABEL)
        {
            DataSet Result;
            DataSet Return = new DataSet();
            string TableName = "WB";
            string KRS = dwTABEL["KRS"].ToString().Trim();
            string LFD = dwTABEL["LFD"].ToString().Trim();
            string POS = dwTABEL["POS"].ToString().Trim();
            try
            {
                orlReadCommand = INTEXDBC.OracleDB.CreateCommand();
                orlReadCommand.CommandText = "SELECT WBBAUFKRS,WBBAUFLFD,WBBAUFPOS, A.WBBSTKNR,TRIM(A.WBBART)||'-'||TRIM(A.WBBFARBE) AS ART,A.WBBSSTKLG*0.001 as PQTY, "
                    + " a.wbbistklg*0.001 as IQTY,A.WBBAUFKRS||'-'||TO_NUMBER(A.WBBAUFLFD)||'-'||TO_NUMBER(A.WBBAUFPOS) AS ORDERNO "
                    + " FROM WBBST A "
                    + " WHERE A.WBBMANDANT = '00'  "
                    + " AND A.WBBAUFKRS = '"+KRS+"' AND A.WBBAUFLFD = '"+LFD+"' AND A.WBBAUFPOS = '"+POS+"' "
                    ;

                Result = INTEXDBC.Connect2Proxy(orlReadCommand);
                if (Result.Tables.Count > 0)
                {
                    Result.Tables[0].TableName = TableName;
                    //byte[] b;
                    //string value;
                    //foreach (DataRow dw in Result.Tables[TableName].Rows)
                    //{
                    //    b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw["DES"].ToString().Trim());
                    //    value = Encoding.GetEncoding("gb2312").GetString(b);
                    //    dw["DES"] = value;
                    //    b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw["STR"].ToString().Trim());
                    //    value = Encoding.GetEncoding("gb2312").GetString(b);
                    //    dw["STR"] = value;
                    //    b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw["YARN"].ToString().Trim());
                    //    value = Encoding.GetEncoding("gb2312").GetString(b);
                    //    dw["YARN"] = value;
                    //}
                }
                Return.Merge(Result);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return Return;
        }
        public DataSet GETDATE(DataRow dwTABEL)
        {
            DataSet Result;
            DataSet Return = new DataSet();
            string TableName = "ABPOS";
            string KRS = dwTABEL["KRS"].ToString().Trim();
            string LFD = "000000"+dwTABEL["LFD"].ToString().Trim();
            LFD = LFD.Substring(LFD.Length - 6, 6);
            string POS = "000"+dwTABEL["POS"].ToString().Trim();
            POS = POS.Substring(POS.Length - 3, 3);          
            try
            {
                orlReadCommand = INTEXDBC.OracleDB.CreateCommand();
                orlReadCommand.CommandText = "SELECT  a.abpaufkrs||A.ABPAUFLFD||A.ABPAUFPOS as ABPART,B.VKPLIEFTERM AS ABPSTARTDAT,C.WAKEAFAKTR AS ABPSOLLMENGE8,SUM(D.wagfdz) AS ABPSOLLMENGE1,D.WAGBEM AS ABAKDPARTIE FROM ABPOS A"
+ " LEFT JOIN VKPOS B ON A.ABPART||'    '=B.VKPART AND A.ABPFARBE=B.VKPFARBE "
+ " LEFT JOIN WASTKE C ON A.ABPART=C.WAKART AND A.ABPFARBE=C.WAKWCOL"
+ " LEFT JOIN WASTGA D ON A.ABPART=D.wagart AND A.ABPFARBE=D.WAGWCOL "
                    //AND D.WAGFOLGEPOS='1'  ,D.WAGBEM,,D.WAGBEM
+ " WHERE a.abpaufkrs = '" + KRS + "' AND A.ABPAUFLFD = '" + LFD + "' AND A.ABPAUFPOS = '" + POS + "' AND B.VKPSA='  '"
              + " GROUP BY a.abpaufkrs||A.ABPAUFLFD||A.ABPAUFPOS, B.VKPLIEFTERM,C.WAKEAFAKTR ,D.WAGBEM ";
                    

                Result = INTEXDBC.Connect2Proxy(orlReadCommand);               
                if (Result.Tables.Count > 0)
                {
                   
                    if (Result.Tables[0].Rows.Count==1)
                    {
                        orlReadCommand = INTEXDBC.OracleDB.CreateCommand();
                        orlReadCommand.CommandText = "SELECT  a.abpaufkrs||A.ABPAUFLFD||A.ABPAUFPOS as ABPART,B.VKPLIEFTERM AS ABPSTARTDAT,C.WAKEAFAKTR AS ABPSOLLMENGE8,SUM(D.wagfdz) AS ABPSOLLMENGE1,D.WAGBEM AS ABAKDPARTIE FROM ABPOS A"
        + " LEFT JOIN VKPOS B ON A.ABPART||'    '=B.VKPART AND A.ABPFARBE=B.VKPFARBE "
        + " LEFT JOIN WASTKE C ON A.ABPART=C.WAKART AND A.ABPFARBE=C.WAKWCOL"
        + " LEFT JOIN WASTGA D ON A.ABPART=D.wagart AND A.ABPFARBE=D.WAGWCOL AND D.WAGFOLGEPOS='1' " // ,D.WAGBEM,,D.WAGBEM
        + " WHERE a.abpaufkrs = '" + KRS + "' AND A.ABPAUFLFD = '" + LFD + "' AND A.ABPAUFPOS = '" + POS + "' AND B.VKPSA='  '"
                      + " GROUP BY a.abpaufkrs||A.ABPAUFLFD||A.ABPAUFPOS, B.VKPLIEFTERM,C.WAKEAFAKTR ,D.WAGBEM ";
                        Result = INTEXDBC.Connect2Proxy(orlReadCommand);          
                    }
                    else if (Result.Tables[0].Rows.Count > 1)
                    {
                        orlReadCommand = INTEXDBC.OracleDB.CreateCommand();
                        orlReadCommand.CommandText = "SELECT  a.abpaufkrs||A.ABPAUFLFD||A.ABPAUFPOS as ABPART,B.VKPLIEFTERM AS ABPSTARTDAT,C.WAKEAFAKTR AS ABPSOLLMENGE8,SUM(D.wagfdz) AS ABPSOLLMENGE1,'1' as ABAKDPARTIE FROM ABPOS A"
        + " LEFT JOIN VKPOS B ON A.ABPART||'    '=B.VKPART AND A.ABPFARBE=B.VKPFARBE "
        + " LEFT JOIN WASTKE C ON A.ABPART=C.WAKART AND A.ABPFARBE=C.WAKWCOL"
        + " LEFT JOIN WASTGA D ON A.ABPART=D.wagart AND A.ABPFARBE=D.WAGWCOL " // ,D.WAGBEM,,D.WAGBEM
        + " WHERE a.abpaufkrs = '" + KRS + "' AND A.ABPAUFLFD = '" + LFD + "' AND A.ABPAUFPOS = '" + POS + "' AND B.VKPSA='  '"
                      + " GROUP BY a.abpaufkrs||A.ABPAUFLFD||A.ABPAUFPOS, B.VKPLIEFTERM,C.WAKEAFAKTR  ";
                        Result = INTEXDBC.Connect2Proxy(orlReadCommand);
                    }
                    Result.Tables[0].TableName = TableName;
                }
                Return.Merge(Result);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return Return;
        }
        public DataSet BuildData(DataRow dwUI)
        {
            /*DataSet SResult = new DataSet();
            SETINTEXDB SReturn = new SETINTEXDB(); */
            DataSet setReturn = new DataSet();
            DataSet setResult = new DataSet();
            try
            {
                setResult = GetAB55(dwUI);
                setReturn.Merge(setResult);
                setResult = GetTT(dwUI);
                setReturn.Merge(setResult);
                setResult = GetWB(dwUI);
                setReturn.Merge(setResult);
                setResult = GETDATE(dwUI);
                setReturn.Merge(setResult);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return setReturn;
        }
      
    }
}